﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace MusicAcademy
{
    class PhoneBook
    {
        public int AddContact(string FirstName, string LastName, int PersonID = -1)
        {
            try
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
                SqlCommand com = new SqlCommand();
                SqlDataReader dreader;

                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = "INSERT INTO Contact (firstName, lastName, personID, academyID, creator, createDate) VALUES (@firstName, @lastName, @personID, @academyID, @creator, @createDate); SELECT ID FROM Contact ORDER BY ID DESC";
                com.Parameters.AddWithValue("@firstName", FirstName);
                com.Parameters.AddWithValue("@lastName", LastName);
                com.Parameters.AddWithValue("@academyID", Program.academyID);
                com.Parameters.AddWithValue("@creator", Program.userID);
                com.Parameters.AddWithValue("@createDate", Globals.Get10CharDate());
                if (PersonID == -1)
                    com.Parameters.AddWithValue("@personID", DBNull.Value);
                else
                    com.Parameters.AddWithValue("@personID", PersonID);

                con.Open();
                dreader = com.ExecuteReader();
                dreader.Read();
                int id = (int)dreader["ID"];
                con.Close();
                return id;
                
            }
            catch (Exception)
            {
                return -1;
            }
        }

        public bool EditContact(int ContactID, string FirstName, string LastName, int PersonID = -1)
        {
            try
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
                SqlCommand com = new SqlCommand();
                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = "UPDATE Contact SET firstName=@firstName, lastName=@lastName, personID=@personID, lastEditor=@lastEditor, editDate=@editDate WHERE ID=@ID";
                com.Parameters.AddWithValue("@firstName", FirstName);
                com.Parameters.AddWithValue("@lastName", LastName);
                com.Parameters.AddWithValue("@lastEditor", Program.userID);
                com.Parameters.AddWithValue("@editDate", Globals.Get10CharDate());
                if (PersonID == -1)
                    com.Parameters.AddWithValue("@personID", DBNull.Value);
                else
                    com.Parameters.AddWithValue("@personID", PersonID);
                com.Parameters.AddWithValue("@ID", ContactID);

                con.Open();
                com.ExecuteNonQuery();
                con.Close();

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool EditContactByPersonID(int PersonID, string FirstName, string LastName)
        {
            try
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
                SqlCommand com = new SqlCommand();
                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = "UPDATE Contact SET firstName=@firstName, lastName=@lastName, lastEditor=@lastEditor, editDate=@editDate WHERE personID=@personID";
                com.Parameters.AddWithValue("@firstName", FirstName);
                com.Parameters.AddWithValue("@lastName", LastName);
                com.Parameters.AddWithValue("@personID", PersonID);
                com.Parameters.AddWithValue("@lastEditor", Program.userID);
                com.Parameters.AddWithValue("@editDate", Globals.Get10CharDate());

                con.Open();
                com.ExecuteNonQuery();
                con.Close();

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public int AddDetail(int ContactID, int ContactType, string DetailValue)
        {
            try
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
                SqlCommand com = new SqlCommand();
                SqlDataReader dreader;
                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = "INSERT INTO ContactDetail (contactID, contactTypeID, value, creator, createDate) VALUES (@contactID, @contactTypeID, @value, @creator, @createDate);SELECT TOP 1 ID FROM ContactDetail ORDER BY ID DESC";
                com.Parameters.AddWithValue("@contactID", ContactID);
                com.Parameters.AddWithValue("@contactTypeID", ContactType);
                com.Parameters.AddWithValue("@value", DetailValue);
                com.Parameters.AddWithValue("@creator", Program.userID);
                com.Parameters.AddWithValue("@createDate", Globals.Get10CharDate());
                con.Open();
                dreader=com.ExecuteReader();
                dreader.Read();
                int ID = int.Parse(dreader["ID"].ToString());
                dreader.Close();
                con.Close();

                return ID;
            }
            catch (Exception)
            {
                return -1;
            }
        }

        public int AddDetail(int ContactID, string ContactType, string DetailValue)
        {
            try
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
                SqlCommand com = new SqlCommand();
                SqlDataReader dreader;
                int cTypeID;

                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = "SELECT ID FROM ContactType WHERE ContactTypeName=@ContactTypeName";
                com.Parameters.AddWithValue("@contactTypeName", ContactType);

                con.Open();
                dreader=com.ExecuteReader();
                dreader.Read();
                cTypeID = Convert.ToInt32(dreader["ID"]);
                dreader.Close();
                con.Close();

                return AddDetail(ContactID, cTypeID, DetailValue);
            }
            catch (Exception)
            {
                return -1;
            }
        }

        public int UpdateDetail(int ContactDetailID, string DetailValue)
        {
            try
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
                SqlCommand com = new SqlCommand();

                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = "UPDATE ContactDetail SET value=@value, lastEditor=@lastEditor, editDate=@editDate WHERE ID=@ID";
                com.Parameters.AddWithValue("@ID", ContactDetailID);
                com.Parameters.AddWithValue("@value", DetailValue);
                com.Parameters.AddWithValue("@lastEditor", Program.userID);
                com.Parameters.AddWithValue("@editDate", Globals.Get10CharDate());

                con.Open();
                com.ExecuteNonQuery();
                con.Close();

                return ContactDetailID;
            }
            catch (Exception)
            {
                return -1;
            }
        }

        public int SaveDetail(int PersonID, int ContactType, string DetailValue)
        {
            try
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
                SqlCommand com = new SqlCommand();
                SqlDataReader dreader;
                int cDetailID = -1;
                int ContactID;

                com.Connection = con;
                com.CommandType = CommandType.Text;
                con.Open();

                com.CommandText = "SELECT ID FROM Contact WHERE personID=@personID";
                com.Parameters.AddWithValue("@personID", PersonID);

                dreader = com.ExecuteReader();
                dreader.Read();
                ContactID = Convert.ToInt32(dreader["ID"]);
                dreader.Close();
                com.Parameters.Clear();

                com.CommandText = "SELECT ID FROM ContactDetail WHERE contactTypeID=@contactTypeID AND contactID=@contactID";
                com.Parameters.AddWithValue("@contactTypeID", ContactType);
                com.Parameters.AddWithValue("@contactID", ContactID);
                
                dreader = com.ExecuteReader();
                if (dreader.Read() && dreader["ID"] != DBNull.Value)
                {
                    cDetailID = Convert.ToInt32(dreader["ID"]);
                }
                dreader.Close();
                con.Close();

                if (cDetailID != -1)
                {
                    return UpdateDetail(cDetailID, DetailValue);
                }
                else
                {
                    return AddDetail(ContactID, ContactType, DetailValue);
                }
            }
            catch (Exception)
            {
                return -1;
            }
        }

        public int SaveDetail(int PersonID, string ContactType, string DetailValue)
        {
            try
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
                SqlCommand com = new SqlCommand();
                SqlDataReader dreader;
                int cTypeID;

                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = "SELECT ID FROM ContactType WHERE ContactTypeName=@ContactTypeName";
                com.Parameters.AddWithValue("@contactTypeName", ContactType);

                con.Open();
                dreader = com.ExecuteReader();
                dreader.Read();
                cTypeID = Convert.ToInt32(dreader["ID"]);
                dreader.Close();
                con.Close();

                return SaveDetail(PersonID, cTypeID, DetailValue);
            }
            catch (Exception)
            {
                return -1;
            }
        }

        public string LoadDetail(int PersonID, int ContactType)
        {
            try
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
                SqlCommand com = new SqlCommand();
                SqlDataReader dreader;
                string DetailValue = "";

                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = "SELECT value FROM ContactDetail INNER JOIN Contact ON Contact.ID= ContactDetail.ContactID WHERE contactTypeID=@contactTypeID AND Contact.personID=@personID";
                com.Parameters.AddWithValue("@contactTypeID", ContactType);
                com.Parameters.AddWithValue("@personID", PersonID);
                con.Open();
                dreader = com.ExecuteReader();
                if (dreader.Read() && dreader["value"] != DBNull.Value)
                {
                    DetailValue = dreader["value"].ToString();
                }
                dreader.Close();
                con.Close();

                return DetailValue;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public string LoadDetail(int PersonID, string ContactType)
        {
            try
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
                SqlCommand com = new SqlCommand();
                SqlDataReader dreader;
                int cTypeID;

                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = "SELECT ID FROM ContactType WHERE ContactTypeName=@ContactTypeName";
                com.Parameters.AddWithValue("@contactTypeName", ContactType);

                con.Open();
                dreader = com.ExecuteReader();
                dreader.Read();
                cTypeID = Convert.ToInt32(dreader["ID"]);
                dreader.Close();
                con.Close();

                return LoadDetail(PersonID, cTypeID);
            }
            catch (Exception)
            {
                return "";
            }
        }

        public void CleanUpPersonContact(int PersonID)
        {
            SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = "DELETE FROM ContactDetail WHERE ID IN (SELECT ContactDetail.ID FROM ContactDetail INNER JOIN Contact ON Contact.ID=ContactDetail.contactID WHERE personID=@personID AND value=N'')";
            com.Parameters.AddWithValue("@personID", PersonID);

            con.Open();
            com.ExecuteNonQuery();
            con.Close();
        }

        public void CleanUpContact(int ContactID)
        {
            SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = "DELETE FROM ContactDetail WHERE ID IN (SELECT ContactDetail.ID FROM ContactDetail WHERE contactID=@contactID AND value=N'')";
            com.Parameters.AddWithValue("@contactID", ContactID);

            con.Open();
            com.ExecuteNonQuery();
            con.Close();
        }

        public int SaveContactDetail(int ContactID, int ContactType, string DetailValue)
        {
            try
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.MusicAcademyConnectionString);
                SqlCommand com = new SqlCommand();
                SqlDataReader dreader;
                int cDetailID = -1;

                com.Connection = con;
                com.CommandType = CommandType.Text;
                con.Open();

                com.CommandText = "SELECT ContactDetail.ID FROM ContactDetail INNER JOIN ContactType ON ContactType.ID=ContactDetail.contactTypeID WHERE contactTypeID=@contactTypeID AND contactID=@contactID AND isSystem=1";
                com.Parameters.AddWithValue("@contactTypeID", ContactType);
                com.Parameters.AddWithValue("@contactID", ContactID);

                dreader = com.ExecuteReader();
                if (dreader.Read() && dreader["ID"] != DBNull.Value)
                {
                    cDetailID = Convert.ToInt32(dreader["ID"]);
                }
                dreader.Close();
                con.Close();

                if (cDetailID != -1)
                {
                    return UpdateDetail(cDetailID, DetailValue);
                }
                else
                {
                    return AddDetail(ContactID, ContactType, DetailValue);
                }
            }
            catch (Exception)
            {
                return -1;
            }
        }
    }
}